﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace _3Layer_CuuHangDienMay.DataAccess
{
    class DataProvider
    {
        private string connectString;
        //Su dung OleDb co the ket noi den cac csdl sql mysql access excel...nhung se cham hon ta dung SqlConnection de ket noi Sql server
        private OleDbConnection connection = null;

        //Khoi tao doi tuong
        public DataProvider()
        {
            connectString = FormMain.connectString;
            OpenConnection();
        }

        //Mo ket noi
        public void OpenConnection()
        {
                connection = new OleDbConnection(connectString);
                connection.Open();
        }

        //dong ket noi
        public void CloseConnection()
        {
            if (connection != null)
                connection.Close();
        }

        //Truy van lay ra mot bang hay khung nhin
        public DataTable ExecuteQuery(string sql)
        {
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, connectString);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            return dt;
        }

        //Cap nhap du lieu: add delete update
        public void ExecuteNonQuery(string sql)
        {
            OpenConnection();
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            cmd.ExecuteNonQuery();
            CloseConnection();
        }

        //Lay ra mot gia tri
        public string ExecuteScalar(String sql)
        {
            OpenConnection();
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            string kq = cmd.ExecuteScalar().ToString();
            CloseConnection();
            return kq;
        }

        //Tra ve mot DataReader doc tung dong du lieu duy nhat 1 lan
        public OleDbDataReader ExecuteReader(String sql)
        {
            OpenConnection();
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            OleDbDataReader reader = cmd.ExecuteReader();
            return reader;
        }

        //Kiem tra su ton tai cua mot gia tri
        public bool CheckExistValue(string nameTable, string nameFiled, string value)
        {
            string sql = "SELECT * FROM " + nameTable + " WHERE " + nameFiled + " = '" + value + "'";
            // Đếm số dòng trả về, nếu > 0  tức tồn tại value
            if (ExecuteQuery(sql).Rows.Count > 0)
            {
                return true;
            }
            return false;
        }
    }
}
